package com.lyf.dao.impl;

import com.lyf.dao.UserDao;
import com.lyf.pojo.User;
import com.lyf.utils.DBTools;
import com.lyf.utils.PageModel;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    QueryRunner qr = DBTools.getQueryRunner();
    @Override
    public User login(String loginname, String password) {
        String sql = "select * from user_inf where loginname = ? and password = ?";
        ResultSetHandler<User> rsh = new BeanHandler<>(User.class);
        User user = null;
        try {
            user = qr.query(sql,rsh,loginname,password);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return user;
    }

    @Override
    public User findPassword(String loginname, String phone) {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "select * from user_inf where loginname = ? and phone = ? and isdelete = 0";
        ResultSetHandler<User> rsh = new BeanHandler<>(User.class);
        User user = null;
        try {
            user = qr.query(sql,rsh,loginname,phone);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return user;
    }

    @Override
    public int selectAdminCount() {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "select count(*)  from user_inf where  role = 1 and isdelete = 0";
        ResultSetHandler<Integer> countHandler = new ScalarHandler<>();
        int count = 0;
        try {
            count = qr.query(sql,countHandler);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return count;
    }

    @Override
    public int selectUserCount() {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "select count(*)  from user_inf where  role = 2 and isdelete = 0";
        ResultSetHandler<Integer> countHandler = new ScalarHandler<>();
        int count = 0;
        try {
            count = qr.query(sql,countHandler);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return count;
    }

    @Override
    public void register(User user) {
        String sql = "insert into user_inf(username,loginname,password,img,phone,createdate) values (?,?,?,?,?,?)";
//        QueryRunner qr = DBTools.getQueryRunner();
        try {
            qr.update(sql,user.getUsername(),user.getLoginname(),user.getPassword(),user.getImg(),user.getPhone(),user.getCreateDate());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void add(User user) {
        String sql = "insert into user_inf(username,loginname,password,img,phone,createdate,role,status) values (?,?,?,?,?,?,?,?)";
//        QueryRunner qr = DBTools.getQueryRunner();
        try {
            qr.update(sql,user.getUsername(),user.getLoginname(),user.getPassword(),
             user.getImg(),user.getPhone(),user.getCreateDate(),user.getRole(),user.getStatus());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void delete(Integer id) {
        String sql = "update user_inf set isdelete = 1 where id = ?";
//        QueryRunner qr = DBTools.getQueryRunner();
        try {
            qr.update(sql,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void update(User user) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("UPDATE user_inf SET username = ? , loginname = ? , role = ? , img = ? , status = ? , phone = ? , isdelete = ? , email = ?");
        List<Object> params = new ArrayList<>(); // 用于存放参数的列表
        params.add(user.getUsername());
        params.add(user.getLoginname());
        params.add(user.getRole());
        params.add(user.getImg());
        params.add(user.getStatus());
        params.add(user.getPhone());
        params.add(user.getIsdelete());
        params.add(user.getEmail());

        sql.append(" where id = ?");
        params.add(user.getId());
        try {
            qr.update(sql.toString(),params.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void updateStatus( Integer id , Integer status) {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "update user_inf set status = ? where id=?";
        try {
            qr.update(sql,status,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void updatePassword(String password,Integer id) {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "update user_inf set password = ? where id=?";
        try {
            qr.update(sql,password,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public List<User> selectAll() {
//        QueryRunner qr = DBTools.getQueryRunner();
        ResultSetHandler<List<User>> rsh = new BeanListHandler<>(User.class);
        String sql = "select * from user_inf";
        List<User> UserList = null;
        try {
            UserList = qr.query(sql, rsh);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return UserList;
    }

    @Override
    public User selectById(Integer id) {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "select * from user_inf where id = ?";
        ResultSetHandler<User> rsh = new BeanHandler<>(User.class);
        User user = null;
        try {
            user = qr.query(sql,rsh,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return user;
    }

    @Override
    public User selectByloginname(String loginname) {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "select * from user_inf where loginname = ?";
        ResultSetHandler<User> rsh = new BeanHandler<>(User.class);
        User user = null;
        try {
            user = qr.query(sql,rsh,loginname);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return user;
    }

    @Override
    public List<User> queryUsers(User user, PageModel model) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("SELECT * FROM user_inf WHERE 1=1");
        List<Object> params = new ArrayList<>(); // 用于存放参数的列表

        // 根据 user 对象的属性添加查询条件
        sql.append(" AND isdelete = 0");
        sql.append(" AND role = 2");
        if (user != null && user.getLoginname() != null && !user.getLoginname().isEmpty()) {
            sql.append(" AND loginname LIKE ?");
            params.add("%" + user.getLoginname() + "%");
        }
        if (user != null && user.getStatus() != null) {
            sql.append(" AND status = ?");
            params.add(user.getStatus());
        }
        // 如果有其他条件，以同样的方式添加到 sql 和 params

        // 添加分页的 LIMIT 和 OFFSET
        int pageSize = model.getPageSize();
        int startRow = model.getStartRow();
        sql.append(" LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add(startRow);

        // 执行查询
        try {
            List<User> users = qr.query(sql.toString(), new BeanListHandler<>(User.class), params.toArray());

            return users;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public int getTotalUserCount(User user) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM user_inf WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 根据 user 对象的属性决定是否添加查询条件
        sql.append(" AND isdelete = 0");
        sql.append(" AND role = 2");
        if (user != null && user.getLoginname() != null && !user.getLoginname().isEmpty()) {
            sql.append(" WHERE loginname LIKE ?");
            params.add("%" + user.getLoginname() + "%");
        }
        if (user != null && user.getStatus() != null) {
            sql.append(" AND status = ?");
            params.add(user.getStatus());
        }
        // 执行查询
        ResultSetHandler<Long> countHandler = new ScalarHandler<>();
        try{
            Long count = qr.query(sql.toString(), countHandler, params.toArray());

            return count.intValue();
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<User> queryUsers2(User user, PageModel model) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("SELECT * FROM user_inf WHERE 1=1");
        List<Object> params = new ArrayList<>(); // 用于存放参数的列表

        // 根据 user 对象的属性添加查询条件
        sql.append(" AND isdelete = 0");
        sql.append(" AND role != 2");
        if (user != null && user.getLoginname() != null && !user.getLoginname().isEmpty()) {
            sql.append(" AND loginname LIKE ?");
            params.add("%" + user.getLoginname() + "%");
        }
        if (user != null && user.getStatus() != null) {
            sql.append(" AND status = ?");
            params.add(user.getStatus());
        }
        // 如果有其他条件，以同样的方式添加到 sql 和 params

        // 添加分页的 LIMIT 和 OFFSET
        int pageSize = model.getPageSize();
        int startRow = model.getStartRow();
        sql.append(" LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add(startRow);

        // 执行查询
        try {
            List<User> users = qr.query(sql.toString(), new BeanListHandler<>(User.class), params.toArray());

            return users;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public int getTotalUserCount2(User user) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM user_inf WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 根据 user 对象的属性决定是否添加查询条件
        sql.append(" AND isdelete = 0");
        sql.append(" AND role != 2");
        if (user != null && user.getLoginname() != null && !user.getLoginname().isEmpty()) {
            sql.append(" WHERE loginname LIKE ?");
            params.add("%" + user.getLoginname() + "%");
        }
        if (user != null && user.getStatus() != null) {
            sql.append(" AND status = ?");
            params.add(user.getStatus());
        }
        // 执行查询
        ResultSetHandler<Long> countHandler = new ScalarHandler<>();
        try{
            Long count = qr.query(sql.toString(), countHandler, params.toArray());

            return count.intValue();
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}
